const connection = require('../app/database.js')
// 所有和数据库相关的操作
class momentServer {

  async create(content , id){
    // 1. 获取动态内容 拼接 statement
    const statement = 'INSERT INTO moment (content , user_id ) VALUES (? , ?);'

    // 2. 执行sql语句 异步的操作
    const [result] = await connection.execute(statement, [content,id])
    return result
  }

  // 获取动态列表
  async getListServer (offset = 0 , size = 10 ){
    // 分页码查询
    // const statement = 'SELECT * FROM moment  LIMIT ? OFFSET ? ;'
    // 联表查询
    const statement = `
      SELECT 
      m.id id , m.content content , m.createAt createTime , m.updateAt updateTime ,
      JSON_OBJECT( 'id' , u.id , 'name' , u.name , 'createTime' , u.createAt , 'updateTime' , u.updateAt ) userInfo,
      (SELECT COUNT(*) FROM  comment WHERE comment.moment_id = m.id ) commentCount
      FROM moment m 
      LEFT JOIN users u ON u.id = m.user_id
      LIMIT ? OFFSET ? ;
    `
    const [result] = await connection.execute(statement , [String(size) , String(offset)] )
    return result
  }

  // 根据id获取某条动态详情
  async detailByidServer(momentId){
    const statement = `
    SELECT 
      m.id id , m.content content , m.createAt createTime , m.updateAt updateTime ,
      JSON_OBJECT( 'id' , u.id , 'name' , u.name , 'createTime' , u.createAt , 'updateTime' , u.updateAt ) userInfo,
      (
        JSON_ARRAYAGG(JSON_OBJECT(
          "id", c.id, "content" , c.content , "commentId" , c.comment_id,
          "user", JSON_OBJECT("id" , users.id , "name" , users.name)
        ))
      ) comments
    FROM moment m 
    LEFT JOIN users u ON u.id = m.user_id
    LEFT JOIN comment c ON c.moment_id = m.id
    LEFT JOIN users ON users.id = c.user_id
    WHERE  m.id = ?
    GROUP BY m.id ;
    `
    const [result] = await connection.execute(statement , [momentId] )
    return result

  }

  // 查看用户操作权限 查看moment表的userId是否和携带的token是否一致 
  async permissionByIdServer(momentId , userId){
    const statement = 'SELECT * From moment WHERE id = ? AND user_id = ?;'
    const [result] = await connection.execute(statement , [ momentId , userId ])
    return result
  }
  
  // 根据动态id修改动态
  async updateByIdServer(content ,id){
    const statement = 'UPDATE moment SET content = ? WHERE id = ? ;'
    const [result] = await connection.execute(statement , [content , id])
    return result
  }


  // 删除动态
  async deleteByIdServer (momentId){
    const statement = 'DELETE FROM moment  WHERE id = ?;'
    const [result] = await connection.execute(statement , [momentId])
    return result
  }

  // 是否存在 moment-label 的关系
  async hasLabel(momentId , labelId){
    const statement = 'SELECT * FROM moment_label WHERE moment_id = ? AND label_id = ?;'
    const [result] = await connection.execute(statement , [momentId , labelId])
    return !!result.length
  }

  // 添加 moment-label 关系
  async addLabel(momentId , labelId){
    const statement = 'INSERT INTO moment_label (moment_id , label_id) VALUES (? ,?) ;'
    const [result] = await connection.execute(statement , [momentId , labelId])
    return result
  }

}
module.exports = new momentServer()